<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>餐馆后厨管理</title>
<script>
// 设置刷新间隔，
setInterval(function() {
    location.reload();
}, 100000000);
</script>
</head>
<body>
<h1>餐馆后厨管理</h1>

<%
    // 数据库连接信息
    String url = "jdbc:mariadb://10.220.140.102:3366/studb11";
    String user = "stua";
    String password = "zHZUA65r";
    

    // 加载并注册JDBC驱动
    Class.forName("org.mariadb.jdbc.Driver");

   
%>
<br>
<h2>等待制作</h2>
 <table width="40%" class="table" border="1">
 <tr>  
        <th width="20%">等待制作菜品</th>
        <th width="20%">总数量</th>
        <th width="20%">桌号</th>
        <th width="20%">操作</th>  
    </tr> 
    <% 
    // 查询making
    Connection conn4 = DriverManager.getConnection(url, user, password);
    String sql4="SELECT name, SUM(amount) as total_quantity, GROUP_CONCAT(table_id) as table_numbers " + " FROM shoppingcar WHERE status = 'waiting' GROUP BY name";
    try (PreparedStatement stmt = conn4.prepareStatement(sql4)){
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
    	%>
    	<tr onclick="fillform(this)" style="cursor:pointer">  
        <td><%=rs.getString("name")%></td>  
        <td><%=rs.getInt("total_quantity")%></td>
        <td><%=rs.getString("table_numbers")%></td>
    	<td>                
                <a href="save4.jsp?btn=del&name=<%=rs.getString("name") %>" onclick="return confirm('请确认是否开始制作？');">开始制作</a><br>   
        </td>
        <tr>
        <% 
        rs.close();
        }
    } %>
 </table>
 <br>
 

<h2>制作中</h2>
 <table width="40%" class="table" border="1">
 <tr>  
        <th width="20%">制作中菜品</th>
        <th width="20%">总数量</th>
        <th width="20%">桌号</th>
        <th width="20%">操作</th>  
    </tr> 
    <% 
    // 查询making
    Connection conn1 = DriverManager.getConnection(url, user, password);
    String sql1="SELECT name, SUM(amount) as total_quantity, GROUP_CONCAT(table_id) as table_numbers " + " FROM shoppingcar WHERE status = 'making' GROUP BY name";
    try (PreparedStatement stmt = conn1.prepareStatement(sql1)){
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
    	%>
    	<tr onclick="fillform(this)" style="cursor:pointer">  
        <td><%=rs.getString("name")%></td>  
        <td><%=rs.getInt("total_quantity")%></td>
        <td><%=rs.getString("table_numbers")%></td>
    	<td>                
                <a href="save1.jsp?btn=del&name=<%=rs.getString("name") %>" onclick="return confirm('请确认是否出菜？');">出菜</a><br>   
        </td>
        <tr>
        <% 
        rs.close();
        }
    } %>
 </table>
 <br>
 
 <h2>制作完成</h2>
 <table width="40%" class="table" border="1">
<tr>  
        <th width="20%">已出菜菜品</th>
        <th width="20%">总数量</th>
        <th width="20%">桌号</th>
        <th width="20%">操作</th>  
    </tr> 
    <% 
    // 查询ready
    Connection conn2 = DriverManager.getConnection(url, user, password);
    String sql2 = "SELECT name, SUM(amount) as total_quantity, GROUP_CONCAT(table_id) as table_numbers " +"FROM shoppingcar WHERE status = 'ready' GROUP BY name";
    try (PreparedStatement stmt = conn2.prepareStatement(sql2)){
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
    	%>
    	<tr onclick="fillform(this)" style="cursor:pointer">  
        <td><%=rs.getString("name")%></td>  
        <td><%=rs.getInt("total_quantity")%></td>
        <td><%=rs.getString("table_numbers")%></td>
    	<td>                
                <a href="save2.jsp?btn=del&name=<%=rs.getString("name") %>" onclick="return confirm('请确认是否上菜？');">上菜</a><br>   
        </td>
        <tr>
        <% 
        rs.close();
        }
    } %>
</table>
<br>





<% 
try (Connection conn = DriverManager.getConnection(url, user, password)) {
       //显示所有已完成的订单
       String sql="SELECT name,order_time " + "FROM shoppingcar WHERE status = '已完成'";
       try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    	   ResultSet rs = stmt.executeQuery();
    	   out.println("<h2>已完成订单</h2>");
           out.println("<table border='1'>");
           out.println("<tr><th>菜品名称</th><th>时间</th></tr>");

           while (rs.next()) {
               String dishName = rs.getString("name");
               String order_time = rs.getString("order_time");
               out.println("<tr><td>" + dishName + "</td><td>"+order_time+"</td></tr>");
           }
           out.println("</table><br>");
       }
       
    } catch (SQLException e) {
        e.printStackTrace();
    }

%>
 <table width="40%" class="table" border="1">
 <tr>  
        <th width="20%">库存不足菜品</th>
        <th width="20%">操作</th>  
    </tr> 
    <% 
    // 查询所有库存为0
    Connection conn = DriverManager.getConnection(url, user, password);
    String sql="SELECT name FROM dishes WHERE stock_quantity = '0'";
    try (PreparedStatement stmt = conn.prepareStatement(sql)){
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
    	%>
    	<tr onclick="fillform(this)" style="cursor:pointer">  
        <td><%=rs.getString("name")%></td>  
    	<td>                
                <a href="save.jsp?btn=del&name=<%=rs.getString("name") %>" onclick="return confirm('请确认是否下架当前食材？');">下架</a><br>   
        </td>
        <tr>
        <% 
        rs.close();
        }
    } %>
 </table>
 <br>
 
 <% 
try (Connection conn5 = DriverManager.getConnection(url, user, password)) {
      //显示已下架菜品
      String Sql="SELECT name " + "FROM dishes WHERE stock_quantity = '//'";
       try (PreparedStatement stmt = conn5.prepareStatement(Sql)) {
    	   ResultSet rs = stmt.executeQuery();
    	   out.println("<h2>已下架菜品</h2>");
           out.println("<table border='1'>");
           out.println("<tr><th>菜品名称</th></tr>");
           while (rs.next()) {
               String dishName = rs.getString("name");
               
               out.println("<tr><td>" + dishName + "</td></tr>");
           }
           out.println("</table><br>");
       }
} catch (SQLException e) {
    e.printStackTrace();
}
%>

 

  <table width="40%" class="table" border="1">
 <tr>  
        <th width="20%">已下架菜品上新</th>
        <th width="20%">操作</th>  
    </tr> 
    <% 
    // 查询所有库存为0
    Connection conn3 = DriverManager.getConnection(url, user, password);
    String sql3="SELECT name FROM dishes WHERE stock_quantity = '//'";
    try (PreparedStatement stmt = conn.prepareStatement(sql3)){
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
    	%>
    	<tr onclick="fillform(this)" style="cursor:pointer">  
        <td><%=rs.getString("name")%></td>  
    	<td>                
                <a href="save3.jsp?btn=del&name=<%=rs.getString("name") %>" onclick="return confirm('请确认是否上新当前食材？');">上新</a><br>   
        </td>
        <tr>
        <% 
        rs.close();
        }
    } %>
 </table>
 <br>

 



</body>
</html>
